﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;
using DocumentFormat.OpenXml.Spreadsheet;
using eCommerce.core;
using eCommerce.core.Helpers;
using eCommerce.db.BORespository;
using eCommerce.db.DataRespository;
using eCommerce.db.SQLDataAccess;

namespace eCommerce.db.DALRespository
{
    public class IeDal
    {
        #region IeDal private
        private static void SetInfo(IDataRecord reader, ref IeEntity o)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                PropertyInfo propertyInfo = DbCommon.GetPropertyInfo(o, reader.GetName(i));
                if (((propertyInfo != null) && (reader[i] != null)) && (reader[i] != DBNull.Value))
                    propertyInfo.SetValue(o, reader[i], null);
                else
                    o[reader.GetName(i)] = reader[i];
            }
        }

        private static void SetInfoList(ref SqlDataReader reader, ref List<IeEntity> ieList)
        {
            while (reader.Read())
            {
                var o = new IeEntity();
                SetInfo(reader, ref o);
                ieList.Add(o);
            }
            reader.Dispose();
        }
        #endregion

        public static List<IeEntity> Search(int type, int storeId, string keyword, int pageIndex, int pageSize, string startDate, string endDate, ref int totalRecords)
        {
            var filter = "1=1";
            
            if (type> 0)
                filter += string.Format(" AND TypeId = {0}", type);

            if (storeId > 0)
                filter += string.Format(" AND StoreId = {0}", storeId);

            if (keyword != "undefined" && !string.IsNullOrEmpty(keyword))
                filter += string.Format(" AND ([Description] LIKE N'%{0}%')", keyword);

            if ((startDate != "undefined" || endDate != "undefined") && !string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                filter += string.Format(" AND CreatedOn Between '{0} 00:00:00' AND '{1} 23:59:59'", Utils.ValidStrDateTime(startDate), Utils.ValidStrDateTime(endDate));

            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstIe = new List<IeEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action",
                                Constant.TableIe, "CreatedOn DESC", "*", filter, pageIndex, pageSize);

                SetInfoList(ref reader, ref lstIe);

                totalRecords = (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action_Count",
                                        Constant.TableIe, filter);

                return lstIe;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Ie_Action_Pagination: {0}", ex.Message));
            }    
        }

        public static int GetQuantityByIeId(int ieId)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {                
                return (int) SqlHelper.ExecuteScalar(
                    DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Ie_GetSumQuantityByIeId", ieId);                
            }
            catch { return 0; }            
        }

        public static int Create(IeEntity o)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {                
                return (int) SqlHelper.ExecuteScalar(
                    DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Ie_Create", o.TypeId, o.StoreId, o.AdminUserId, o.Description);                
            }
            catch (Exception ex)
            {
                throw new Exception(String.Format(DbCommon.DatabaseSchema + "Ie_Create: {0}", ex.Message));
            }
        }

        public static void Delete(int id)
        {
            try
            {
                if (id > 0)
                    SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Ie_Delete", id);
            }
            catch (Exception ex)
            {
                throw new Exception(String.Format(DbCommon.DatabaseSchema + "Ie_Delete: {0}", ex.Message));
            }
        }

        public static List<IeEntity> GetDataImportExport(int sizeType, int storeId, string description, Worksheet worksheet, SharedStringTable sharedString, ref string errorMessage)
        {
            var result = new List<IeEntity>();

            IEnumerable<Row> dataRows =
                from row in worksheet.Descendants<Row>()
                where row.RowIndex > 1
                select row;

            var count = 0;
            var lstSize = SizeBo.GetListByType(sizeType);

            if (lstSize.Count > 0)
            {
                foreach (Row row in dataRows)
                {
                    count++;
                    IEnumerable<string> textValues =
                        from cell in row.Descendants<Cell>()
                        where cell.CellValue != null
                        select
                            (cell.DataType != null
                             && cell.DataType.HasValue
                             && cell.DataType == CellValues.SharedString
                                 ? sharedString.ChildElements[
                                     Int32.Parse(cell.CellValue.InnerText)].InnerText
                                 : cell.CellValue.InnerText);

                    if (textValues.Count() > 0)
                    {
                        try
                        {
                            var textArray = textValues.ToArray();

                            var o = new IeEntity();
                            o.LstIeDetailEntity = new List<IeDetailEntity>();

                            o.StoreId     = storeId;
                            o.TypeId      = storeId > 1 ? (int)SystemType.Store : (int)SystemType.Warehouse;
                            o.AdminUserId = HttpContext.Current.User.Identity.Name.Replace(Utils.GetSetting("SitePrefix", SettingType.String).ToString(), String.Empty);
                            o.Description = description;

                            for (int i = 0; i < lstSize.Count; i++)
                            {
                                var productId = Utils.ToInt32(textArray[0]);

                                var j = i + 3;
                                var quantity = Utils.ToInt32(textArray[j]);

                                if (productId > 0 && quantity > 0)
                                {
                                    var ieDetail  = new IeDetailEntity();

                                    o.ProductId         = productId;                                                                        
                                    ieDetail.SizeId     = lstSize[i].SizeId;
                                    ieDetail.SizeName   = lstSize[i].Name;                                                                                        
                                    ieDetail.ParentId   = 0;
                                    ieDetail.CategoryId = 0;
                                    ieDetail.ProductId  = productId;
                                    ieDetail.Quantity   = quantity;                                            

                                    o.LstIeDetailEntity.Add(ieDetail);
                                }
                            }

                            result.Add(o);
                        }
                        catch (Exception ex)
                        {
                            errorMessage += String.Format("<tr><td colspan=\"{0}\">Lỗi dòng: {1} - {2}</td></tr>", lstSize.Count + 2, count, ex.Message);
                        }
                    }
                    else
                        break;
                }
            }

            return result;
        }

        public static List<IeEntity> ExecuteImportExport(int sizeType, int storeId, string description, Worksheet worksheet, SharedStringTable sharedString, ref string errorMessage)
        {
            var result = new List<IeEntity>();

            IEnumerable<Row> dataRows =
                from row in worksheet.Descendants<Row>()
                where row.RowIndex > 1
                select row;

            var count = 0;
            var lstSize = SizeBo.GetListByType(sizeType);

            var o = new IeEntity();
            o.LstIeDetailEntity = new List<IeDetailEntity>();

            o.StoreId = storeId;
            o.TypeId = storeId > 1 ? (int)SystemType.Store : (int)SystemType.Warehouse;
            o.AdminUserId = HttpContext.Current.User.Identity.Name.Replace(Utils.GetSetting("SitePrefix", SettingType.String).ToString(), String.Empty);
            o.Description = description;

            var iIe = IeBo.Create(o);

            if (lstSize.Count > 0)
            {
                foreach (Row row in dataRows)
                {
                    count++;
                    IEnumerable<string> textValues =
                        from cell in row.Descendants<Cell>()
                        where cell.CellValue != null
                        select
                            (cell.DataType != null
                             && cell.DataType.HasValue
                             && cell.DataType == CellValues.SharedString
                                 ? sharedString.ChildElements[
                                     Int32.Parse(cell.CellValue.InnerText)].InnerText
                                 : cell.CellValue.InnerText);

                    if (textValues.Count() > 0)
                    {
                        try
                        {
                            var textArray = textValues.ToArray();
                            
                            for (int i = 0; i < lstSize.Count; i++)
                            {
                                var productId = Utils.ToInt32(textArray[0]);

                                var j = i + 3;
                                var quantity = Utils.ToInt32(textArray[j]);

                                if (productId > 0 && quantity > 0)
                                {
                                    var ieDetail = new IeDetailEntity();

                                    o.ProductId = productId;

                                    ieDetail.IeId       = iIe;
                                    ieDetail.SizeId     = lstSize[i].SizeId;
                                    ieDetail.SizeName   = lstSize[i].Name;
                                    ieDetail.ParentId   = 0; 
                                    ieDetail.CategoryId = 0; 
                                    ieDetail.ProductId  = productId;
                                    ieDetail.Quantity   = quantity;

                                    o.LstIeDetailEntity.Add(ieDetail);        
                                    IeDetailBo.Create(ieDetail);                                                                
                                }
                            }

                            result.Add(o);
                        }
                        catch (Exception ex)
                        {
                            errorMessage += String.Format("<tr><td colspan=\"{0}\">Lỗi dòng: {1} - {2}</td></tr>", lstSize.Count + 2, count, ex.Message);
                        }
                    }
                    else
                        break;
                }
            }

            return result;
        }
    }
}
